﻿using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Collections.Generic;
using System.Text;
using Entities;

namespace Distribution.Data
{
    public class DLookUp
    {
        #region - Public methods -
        
        public static List<ELookUp> GetAll(LookUpType lookUpType)
        {
            SqlCeConnection _Con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                _Con = Helper.GetConnection();

                query.Append("SELECT LookupId, LookupValue, LookupText, LookupTypeFk, LookupFk ");
                query.Append("FROM tb_Lookups ");
                query.Append("WHERE LookupTypeFk = @LookupTypeFk ");
                query.Append("ORDER BY LookupText ");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), _Con);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateNVarCharParameter("@LookupTypeFk", 20, ParameterDirection.Input, false, lookUpType.ToString()));

                SqlCeDataReader dr = command.ExecuteReader();

                List<ELookUp> lookUps = new List<ELookUp>();

                while (dr.Read())
                {
                    ELookUp lookUp = new ELookUp();

                    lookUp.Id = dr.GetInt32(dr.GetOrdinal("LookupId"));
                    lookUp.Type = lookUpType;

                    if (!dr.IsDBNull(dr.GetOrdinal("LookupFk")))
                    {
                        lookUp.LookUp = new ELookUp();
                        lookUp.LookUp.Id = dr.GetInt32(dr.GetOrdinal("LookupFk"));
                    }

                    lookUp.Text = dr.GetString(dr.GetOrdinal("LookupText"));
                    lookUp.Value = dr.GetInt32(dr.GetOrdinal("LookupValue"));

                    lookUps.Add(lookUp);
                }

                return lookUps;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }            
        }

        public static List<ELookUp> GetAllTops()
        {
            SqlCeConnection _Con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                _Con = Helper.GetConnection();

                query.Append("SELECT LookupId, LookupValue, LookupText, LookupTypeFk, LookupFk ");
                query.Append("FROM tb_Lookups ");
                query.Append("WHERE LookupTypeFk = 'Top' ");
                query.Append("ORDER BY LookupId ");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), _Con);
                command.CommandType = CommandType.Text;

                SqlCeDataReader dr = command.ExecuteReader();

                List<ELookUp> lookUps = new List<ELookUp>();

                while (dr.Read())
                {
                    ELookUp lookUp = new ELookUp();

                    lookUp.Id = dr.GetInt32(dr.GetOrdinal("LookupId"));
                    lookUp.Type = LookUpType.Top;

                    if (!dr.IsDBNull(dr.GetOrdinal("LookupFk")))
                    {
                        lookUp.LookUp = new ELookUp();
                        lookUp.LookUp.Id = dr.GetInt32(dr.GetOrdinal("LookupFk"));
                    }

                    lookUp.Text = dr.GetString(dr.GetOrdinal("LookupText"));
                    lookUp.Value = dr.GetInt32(dr.GetOrdinal("LookupValue"));

                    lookUps.Add(lookUp);
                }

                return lookUps;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static ELookUp GetByIdType (int id, LookUpType lookUpType)
        {
            SqlCeConnection _Con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                _Con = Helper.GetConnection();

                query.Append("SELECT LookupId, LookupValue, LookupText, LookupTypeFk, LookupFk ");
                query.Append("FROM tb_Lookups ");
                query.Append("WHERE LookupTypeFk = @LookupTypeFk AND LookupValue = @LookupId ");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), _Con);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateNVarCharParameter("@LookupTypeFk", 20, ParameterDirection.Input, false, lookUpType.ToString()));
                command.Parameters.Add(Helper.CreateIntParameter("@LookupId", ParameterDirection.Input, false, id));

                SqlCeDataReader dr = command.ExecuteReader();
                ELookUp lookUp = new ELookUp();

                if (dr.Read())
                {
                    lookUp.Id = dr.GetInt32(dr.GetOrdinal("LookupId"));
                    lookUp.Type = lookUpType;

                    if (!dr.IsDBNull(dr.GetOrdinal("LookupFk")))
                    {
                        lookUp.LookUp = new ELookUp();
                        lookUp.LookUp.Id = dr.GetInt32(dr.GetOrdinal("LookupFk"));
                    }

                    lookUp.Text = dr.GetString(dr.GetOrdinal("LookupText"));
                    lookUp.Value = dr.GetInt32(dr.GetOrdinal("LookupValue"));
                }

                return lookUp;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static void Insert(ELookUp lookUpEntity)
        {
            SqlCeConnection connection = null;
            SqlCeTransaction tx = null;
            var query = new StringBuilder();

            try
            {
                connection = Helper.GetConnection();
                tx = connection.BeginTransaction();
                SqlCeCommand command = null;

                //Gest the last Top Id
                query = new StringBuilder("SELECT MAX(LookupValue) AS id FROM tb_Lookups WHERE LookUpTypeFk = 'Top'");

                command = new SqlCeCommand(query.ToString(), connection, tx);
                command.CommandType = CommandType.Text;
                var lastId = (int)command.ExecuteScalar();
                lookUpEntity.Value = lastId + 1;

                query = new StringBuilder("INSERT INTO tb_Lookups (LookupValue, LookupText, LookupFk, LookupTypeFk) VALUES ");
                query.Append(" (@LookupValue, @LookupText, @LookupFk, @LookupTypeFk);");

                command = new SqlCeCommand(query.ToString(), connection, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@LookupValue", ParameterDirection.Input, false, lookUpEntity.Value));
                command.Parameters.Add(Helper.CreateNVarCharParameter("@LookupText", 100, ParameterDirection.Input, false, lookUpEntity.Text));
                command.Parameters.Add(Helper.CreateIntParameter("@LookupFk", ParameterDirection.Input, false, DBNull.Value));
                command.Parameters.Add(Helper.CreateNVarCharParameter("@LookupTypeFk", 20, ParameterDirection.Input, false, lookUpEntity.Type.ToString()));

                command.ExecuteNonQuery();

                //Gets the last inserted Top 
                query = new StringBuilder("SELECT MAX(LookupId) AS id FROM tb_Lookups");

                command = new SqlCeCommand(query.ToString(), connection, tx);
                command.CommandType = CommandType.Text;
                lastId = (int)command.ExecuteScalar();
                lookUpEntity.Id = lastId;

                if (tx != null)
                    tx.Commit();
            }
            catch
            {
                if (tx != null)
                    tx.Rollback();

                throw;
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }


        #endregion
    }
}
